﻿//======================================================================
//
//        Copyright (C) 苏州逸仙软件    
//        All rights reserved
//
//        filename :D_T_Category_Exp.cs
//        description :所有可分类的信息类别
//
//        created by Jason at  2010-12-12 16：55
//
//======================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using huaguwang;

namespace huaguwang
{
    public class D_T_Category_Exp : D_T_Category
    {
        /// <summary>
        /// 获取所有类别信息
        /// </summary>
        /// <returns></returns>
        public List<M_T_Category> GetAllCate()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT ");
            strSQL.Append("ID,CateName FROM  dbo.T_Category WHERE  Flag = 1  ORDER BY OrderPath");
            var List = MSSQLHelper.SQLConType.C1.Exp_GetList(strSQL.ToString(),
                DR => new M_T_Category()
                {
                    id = DR["id"].Exp_IntTryParse(),
                    CateName = DR["CateName"].Exp_Trim()
                });
            return List;
        }

        /// <summary>
        /// 添加或者修改类别数据时检查记录是否存在
        /// id判断是添加还是修改
        /// </summary>
        public bool ExistsExp(string CateName, int PetId, int id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT ID FROM T_Category WHERE CateName=@CateName AND Flag = 1 and ParentId = @PetId ");
            strSQL.Append((id > 0) ? " and ID <> @ID " : "");
            SqlParameter[] parms = {
                                        new SqlParameter("@CateName",SqlDbType.NVarChar,50),
                                        new SqlParameter("@PetId",SqlDbType.Int,4),
                                        new SqlParameter("@ID",SqlDbType.Int,4)};
            parms[0].Value = CateName;
            parms[1].Value = PetId;
            parms[2].Value = id;
            return MSSQLHelper.SQLConType.C1.Exp_Exists(strSQL.ToString(), parms);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int AddExp(M_T_Category model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("INSERT INTO T_Category(");
            strSQL.Append("CateName,OrderBy,Depth,ParentId,ParentPath,ChildPath,Explain,CateContent,Flag,DescriptionWord,KeyWords,QueryKey,OrderPath)");
            strSQL.Append(" VALUES (");
            strSQL.Append("@CateName,@OrderBy,@Depth,@ParentId,@ParentPath,@ChildPath,@Explain,@CateContent,@Flag,@DescriptionWord,@KeyWords,@QueryKey,@OrderPath)");
            strSQL.Append(" UPDATE T_Category SET");
            strSQL.Append(" ParentPath = dbo.f_HGDB_CategoryPList(id),ChildPath=dbo.f_HGDB_CategorySList(id),OrderPath = dbo.f_HGDB_CategoryOrder(id)");
            strSQL.Append(" WHERE ID = @@IDENTITY");
            strSQL.Append("; SELECT @@IDENTITY");
            SqlParameter[] parms = {
					new SqlParameter("@CateName", SqlDbType.NVarChar,50),
					new SqlParameter("@OrderBy", SqlDbType.Int,4),
					new SqlParameter("@Depth", SqlDbType.Int,4),
					new SqlParameter("@ParentId", SqlDbType.Int,4),
					new SqlParameter("@ParentPath", SqlDbType.NVarChar,1000),
					new SqlParameter("@ChildPath", SqlDbType.NVarChar,1000),
					new SqlParameter("@Explain", SqlDbType.NVarChar,1000),
					new SqlParameter("@CateContent", SqlDbType.Text),
					new SqlParameter("@Flag", SqlDbType.Bit,1),
					new SqlParameter("@DescriptionWord", SqlDbType.NVarChar,200),
					new SqlParameter("@KeyWords", SqlDbType.NVarChar,200),
					new SqlParameter("@QueryKey", SqlDbType.NVarChar,200),
					new SqlParameter("@OrderPath", SqlDbType.NVarChar,1000)};
            parms[0].Value = model.CateName;
            parms[1].Value = model.OrderBy;
            parms[2].Value = model.Depth;
            parms[3].Value = model.ParentId;
            parms[4].Value = model.ParentPath;
            parms[5].Value = model.ChildPath;
            parms[6].Value = model.Explain;
            parms[7].Value = model.CateContent;
            parms[8].Value = model.Flag;
            parms[9].Value = model.DescriptionWord;
            parms[10].Value = model.KeyWords;
            parms[11].Value = model.QueryKey;
            parms[12].Value = model.OrderPath;

            object obj = MSSQLHelper.SQLConType.C1.Exp_GetSingle(strSQL.ToString(), parms);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int UpdateExp(M_T_Category model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("update T_Category set ");
            strSQL.Append("CateName=@CateName,");
            strSQL.Append("OrderBy=@OrderBy,");
            strSQL.Append("Depth=@Depth,");
            strSQL.Append("ParentId=@ParentId,");
            strSQL.Append("ParentPath=dbo.f_HGDB_CategoryPList(id),");
            strSQL.Append("ChildPath=dbo.f_HGDB_CategorySList(id),");
            strSQL.Append("Explain=@Explain,");
            strSQL.Append("CateContent=@CateContent,");
            strSQL.Append("DescriptionWord=@DescriptionWord,");
            strSQL.Append("KeyWords=@KeyWords,");
            strSQL.Append("QueryKey=@QueryKey,");
            strSQL.Append("OrderPath=dbo.f_HGDB_CategoryOrder(id)");
            strSQL.Append(" where ID=@ID ");
            SqlParameter[] parms = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@CateName", SqlDbType.NVarChar,50),
					new SqlParameter("@OrderBy", SqlDbType.Int,4),
					new SqlParameter("@Depth", SqlDbType.Int,4),
					new SqlParameter("@ParentId", SqlDbType.Int,4),
					new SqlParameter("@ParentPath", SqlDbType.NVarChar,1000),
					new SqlParameter("@ChildPath", SqlDbType.NVarChar,1000),
					new SqlParameter("@Explain", SqlDbType.NVarChar,1000),
					new SqlParameter("@CateContent", SqlDbType.Text),
					new SqlParameter("@Flag", SqlDbType.Bit,1),
					new SqlParameter("@DescriptionWord", SqlDbType.NVarChar,200),
					new SqlParameter("@KeyWords", SqlDbType.NVarChar,200),
					new SqlParameter("@QueryKey", SqlDbType.NVarChar,200),
					new SqlParameter("@OrderPath", SqlDbType.NVarChar,1000)};
            parms[0].Value = model.id;
            parms[1].Value = model.CateName;
            parms[2].Value = model.OrderBy;
            parms[3].Value = model.Depth;
            parms[4].Value = model.ParentId;
            parms[5].Value = model.ParentPath;
            parms[6].Value = model.ChildPath;
            parms[7].Value = model.Explain;
            parms[8].Value = model.CateContent;
            parms[9].Value = model.Flag;
            parms[10].Value = model.DescriptionWord;
            parms[11].Value = model.KeyWords;
            parms[12].Value = model.QueryKey;
            parms[13].Value = model.OrderPath;

            var rows = MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strSQL.ToString(), parms);
            if (rows > 0)
            {
                return model.id;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 删除节点后，更新ChildPath
        /// </summary>
        private int UpdateExp(int id)
        {
            var model = GetModel(id);
            if (model == null)
            {
                return 0;
            }
            var idList = model.ParentPath.Exp_Trim();
            idList = idList.Substring(0, idList.LastIndexOf(','));
            UpdateExp(idList);
            return 0;
        }

        /// <summary>
        /// 更新IdString中结点的OrderPath，ParentPath，ChildPath
        /// </summary>
        private int UpdateExp(string IdString)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("UPDATE T_Category SET ");
            strSQL.Append("ParentPath=dbo.f_HGDB_CategoryPList(id),");
            strSQL.Append("ChildPath=dbo.f_HGDB_CategorySList(id),");
            strSQL.Append("OrderPath=dbo.f_HGDB_CategoryOrder(id)");
            strSQL.Append(" WHERE id IN(" + IdString + ")");
            return MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strSQL.ToString());
        }

        /// <summary>
        /// 用事务删除类型数据
        /// </summary>
        public int DelExp(int id)
        {
            var Refitem = MSSQLHelper.SQLConType.C1.ExecuteSqlTran((com, tran) =>
            {
                var model = GetModel(id);
                if (model == null)
                {
                    return 0;
                }
                var idList = model.ParentPath.Exp_Trim();
                idList = idList.Substring(0, idList.LastIndexOf(','));

                string strSql1 = " update T_Category set flag=0 where id=@id ";
                com.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Value = id });
                com.CommandText = strSql1;
                int intID = com.ExecuteNonQuery();
                com.Parameters.Clear();

                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("UPDATE T_Category SET ");
                strSQL.Append("ParentPath=dbo.f_HGDB_CategoryPList(id),");
                strSQL.Append("ChildPath=dbo.f_HGDB_CategorySList(id),");
                strSQL.Append("OrderPath=dbo.f_HGDB_CategoryOrder(id)");
                strSQL.Append(" WHERE id IN(" + idList + ")");

                com.CommandText = strSQL.ToString();
                intID = 0;
                intID = com.ExecuteNonQuery();
                if (0 >= intID)
                {
                    tran.Rollback();
                    return 0;
                }
                tran.Commit();
                return id;
            }
           );
            return Refitem;
        }

        /// <summary>
        /// 判断当前结点是否还有子结点
        /// 有则为有效结点
        /// </summary>
        public bool HasChildNodes(int id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT count(1) FROM  T_Category ");
            strSQL.Append(" WHERE ParentID=@ID and Flag = 1 ");
            SqlParameter[] parms = {
                                        new SqlParameter("@ID",SqlDbType.Int,4)};
            parms[0].Value = id;
            return MSSQLHelper.SQLConType.C1.Exp_Exists(strSQL.ToString(), parms);
        }

        /// <summary>
        /// 查询当前父ID下的最大的排序号
        /// </summary>
        public int GetMaxOrder(int parentID)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT MAX(OrderBy) FROM T_Category");
            strSQL.Append(" WHERE ParentID=@pid and Flag = 1 ");
            SqlParameter[] parms = {
					new SqlParameter("@pid", SqlDbType.Int,4)};
            parms[0].Value = parentID;

            DataTable cdt = MSSQLHelper.SQLConType.C1.Exp_Query(strSQL.ToString(), parms).Tables[0];
            return cdt.Rows[0][0].Exp_IntTryParse();
        }

        /// <summary>
        /// 查询所有类型数据 主要用于下拉树的数据源
        /// </summary>
        public List<M_T_Category> GetClassList()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT ");
            strSQL.Append("ID,CateName,Depth,ParentId");
            strSQL.Append(" FROM (SELECT  ID, CateName, OrderBy, Depth, ParentId, ParentPath, ChildPath, Explain,CateContent, Flag, DescriptionWord, KeyWords,  QueryKey, OrderPath ");
            strSQL.Append("  FROM  T_Category AS a ");
            strSQL.Append(" WHERE  (Flag = 1)  UNION ALL ");
            strSQL.Append("SELECT     0 AS Expr1, '根类别' AS Expr2, 0 AS Expr3, '' AS Expr4, '-1' AS Expr5, '0' AS Expr6, '0' AS Expr7, '无' AS Expr8, '无' AS Expr9, 1 AS Expr10, '无' AS Expr12, '无' AS Expr13, '无' AS Expr14, '0' AS Expr15) AS a_1 ");
            strSQL.Append(" ORDER BY OrderPath ");

            var List = MSSQLHelper.SQLConType.C1.Exp_GetList(strSQL.ToString(),
                DR => new M_T_Category()
                {
                    id = DR["id"].Exp_IntTryParse(),
                    CateName = DR["CateName"].Exp_Trim(),
                    Depth = DR["Depth"].Exp_IntTryParse(),
                    ParentId = DR["ParentId"].Exp_IntTryParse()
                });
            return List;
        }

        /// <summary>
        /// 查询所有类型数据 主要用于下拉树的数据源
        /// </summary>
        public List<M_T_Category> GetCateList()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT  ID, CateName, OrderBy, Depth, ParentId, ParentPath, ChildPath, Explain,CateContent, Flag, DescriptionWord, KeyWords,  QueryKey, OrderPath ");
            strSQL.Append("  FROM  T_Category  ");
            strSQL.Append(" WHERE  (Flag = 1)   ");
            strSQL.Append(" ORDER BY OrderPath");

            var List = MSSQLHelper.SQLConType.C1.Exp_GetList(strSQL.ToString(),
                DR => new M_T_Category()
                {
                    id = DR["id"].Exp_IntTryParse(),
                    CateName = DR["CateName"].Exp_Trim(),
                    Depth = DR["Depth"].Exp_IntTryParse(),
                    ParentId = DR["ParentId"].Exp_IntTryParse()
                });
            return List;
        }

        /// <summary>
        /// 检测该类别信息是否还有效
        /// </summary>
        public bool IsEffective(int classid)
        {
            var strSql = @"select sum(c) from (
                                select Count(1) as c from dbo.T_Experience where Flag = 1 and CateID = @classid
                                union all
                                select Count(1) as c from dbo.T_Formula where Flag = 1 and CateID = @classid
                                union all
                                select Count(1) as c from dbo.T_Knowledge where Flag = 1 and CateID = @classid
                                union all
                                select Count(1) as c from dbo.T_EBook where Flag = 1 and CategoryID = @classid
                                union all
                                select Count(1) as c from dbo.T_Video where Flag = 1 and CategoryID = @classid
                                ) as tab";

            var vResult = MSSQLHelper.SQLConType.C1.Exp_Exists(
                strSql,
                new SqlParameter("@classid", SqlDbType.Int, 4) { Value = classid }
                );
            return vResult;
        }

        #region 类别移动

        /// <summary>
        /// 通过标记和ID来执行上移/下移类别信息
        /// </summary>
        /// <param name="id">类别ID</param>
        /// <param name="flag">标记: true上移,false下移</param>
        public bool MoveClass(int id, bool flag)
        {
            var model = GetModel(id);//获取当前结点信息
            var curOrderBy = model.OrderBy;//记录当前结点的OrderBy

            if (flag)
            {
                var pModel = GetPreModel(model);

                if (pModel == null)
                {
                    return false;
                }
                model.OrderBy = pModel.OrderBy;

                pModel.OrderBy = curOrderBy;
                UpdateExp(pModel);//更新上一结点信息
                UpdateExp(pModel.ChildPath);//更新上一结点所有子结点信息
            }
            else
            {
                var nModel = GetNextModel(model);

                if (nModel == null)
                {
                    return false;
                }
                model.OrderBy = nModel.OrderBy;

                nModel.OrderBy = curOrderBy;
                UpdateExp(nModel);//更新下结点信息
                UpdateExp(nModel.ChildPath);//更新下一结点所有子结点信息
            }

            UpdateExp(model);//更新当前结点信息
            UpdateExp(model.ChildPath);//更新当前结点所有子结点信息

            return true;
        }

        /// <summary>
        /// 查询当前结点的下一结点信息
        /// </summary>
        private M_T_Category GetNextModel(M_T_Category model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * ");
            strSql.Append(" from dbo.T_Category ");
            strSql.Append(" where ID in (");
            strSql.Append(" select ID from dbo.T_Category ");
            strSql.Append("where  Flag = 1 and  ParentID =" + model.ParentId);
            strSql.Append(" and   OrderBy in (select Min(OrderBy) from dbo.T_Category  ");
            strSql.Append(" where  Flag =1 and OrderBy >" + model.OrderBy + " and  ParentID =" + model.ParentId + " ))");

            var list = huaguwang.MSSQLHelper.SQLConType.C1.Exp_GetList(
                strSql.ToString(),
                dr => new M_T_Category()
                {
                    id = dr["id"].Exp_IntTryParse(),
                    ParentId = dr["ParentId"].Exp_IntTryParse(),
                    OrderBy = dr["OrderBy"].Exp_IntTryParse(),
                    Depth = dr["Depth"].Exp_IntTryParse(),
                    ChildPath = dr["ChildPath"].Exp_Trim(),
                    OrderPath = dr["OrderPath"].Exp_Trim(),
                    CateName = dr["CateName"].Exp_Trim(),
                    CateContent = dr["CateContent"].Exp_Trim(),
                    DescriptionWord = dr["DescriptionWord"].Exp_Trim(),
                    Explain = dr["Explain"].Exp_Trim(),
                    KeyWords = dr["KeyWords"].Exp_Trim(),
                    ParentPath = dr["ParentPath"].Exp_Trim(),
                    QueryKey = dr["QueryKey"].Exp_Trim(),
                    Flag = true
                });
            return (list.Count > 0) ? list[0] : null;
        }

        /// <summary>
        /// 查询当前结点的上一结点信息
        /// </summary>
        private M_T_Category GetPreModel(M_T_Category model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * ");
            strSql.Append(" from dbo.T_Category ");
            strSql.Append(" where ID in (");
            strSql.Append(" select ID from dbo.T_Category ");
            strSql.Append("where  Flag = 1 and  ParentID =" + model.ParentId);
            strSql.Append(" and   OrderBy in (select Max(OrderBy) from dbo.T_Category  ");
            strSql.Append(" where  Flag =1 and OrderBy <" + model.OrderBy + " and  ParentID =" + model.ParentId + "))");

            var list = huaguwang.MSSQLHelper.SQLConType.C1.Exp_GetList(
                strSql.ToString(),
                dr => new M_T_Category()
                {
                    id = dr["id"].Exp_IntTryParse(),
                    ParentId = dr["ParentId"].Exp_IntTryParse(),
                    OrderBy = dr["OrderBy"].Exp_IntTryParse(),
                    Depth = dr["Depth"].Exp_IntTryParse(),
                    ChildPath = dr["ChildPath"].Exp_Trim(),
                    OrderPath = dr["OrderPath"].Exp_Trim(),
                    CateName = dr["CateName"].Exp_Trim(),
                    CateContent = dr["CateContent"].Exp_Trim(),
                    DescriptionWord = dr["DescriptionWord"].Exp_Trim(),
                    Explain = dr["Explain"].Exp_Trim(),
                    KeyWords = dr["KeyWords"].Exp_Trim(),
                    ParentPath = dr["ParentPath"].Exp_Trim(),
                    QueryKey = dr["QueryKey"].Exp_Trim(),
                    Flag = true
                });
            return (list.Count > 0) ? list[0] : null;
        }

        #endregion

        /// <summary>
        /// 获取所有叶子类别的ID,CateName
        /// 主要用于前台的类别
        /// </summary>
        public List<M_T_Category> GetChildrenNode()
        {
            var sql = @"select  id,CateName from dbo.T_Category
                                where Flag = 1 and id
                                not in(select distinct(parentid) from dbo.T_Category where Flag = 1)";

            var List = MSSQLHelper.SQLConType.C1.Exp_GetList(sql.ToString(),
             DR => new M_T_Category()
             {
                 id = DR["id"].Exp_IntTryParse(),
                 CateName = DR["CateName"].Exp_Trim()
             });
            return List;
        }
    }
}
